'''
Created on 2022/08/10

@author: zz
'''
import os, sys
import json
import pandas as pd
import openpyxl
import ntpath


def write_excel_xlsx(path, sheet_name, value):
    index = len(value)
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    # sheet = workbook.create_sheet(sheet_name)
    sheet.title = sheet_name
    for i in range(0, index):
        for j in range(0, len(value[i])):
            sheet.cell(row=i+1, column=j+1, value=str(value[i][j]))
    workbook.save(path)
    print("xlsx格式表格写入数据成功！")

def addExcel(path, value, sheet):
    '''
	:param sheet:sheet的名称
    :param path:写入excel的路径
    :param value: 追加的数据
    :return:
    '''
    wb = openpyxl.load_workbook(path)
    wb.create_sheet(sheet)
    ws = wb[sheet]
    for ss in value:
        ws.append(ss)
    wb.save(path)
    print("写入成功")


def read_excel_xlsx(path, sheet_name):
    workbook = openpyxl.load_workbook(path)
    # sheet = wb.get_sheet_by_name(sheet_name)这种方式已经弃用，不建议使用
    sheet = workbook[sheet_name]
    for row in sheet.rows:
        for cell in row:
            print(cell.value, "\t", end="")
        print()


if __name__ == '__main__':
    retry = 0
    while True:
        input_path = input('Please input file directory = ').strip()
        # input_path = './header'
        if retry > 3:
            print(f'error exit.')
            sys.exit(1)
        elif not os.path.isdir(input_path):
            retry += 1
            print(f'{input_path} is not directory.')
            continue
        else:
            break

    output_file = "./spec_ver_collect.xlsx"

    if os.path.isdir(input_path):
        os.chdir(input_path)
        add_sheet = 0
        for dir in os.listdir(input_path):
            # print(dir)
            dir_path = os.path.join(input_path, dir)
            if os.path.isdir(dir_path):
                file_date = [[""]*20 for i in range(2000)]
                index = 0
                columns = 0
                file_path = os.path.join(input_path, dir)
                for path, dirs, files in os.walk(file_path):
                    index += 1
                    # print(path)
                    # print(dirs)
                    # print(files)
                    if files:
                        for file in files:
                            columns = 0
                            filelen = len(path.replace(input_path,"").split("\\"))+1
                            for i in range(0,filelen-1):
                                file_date[index][columns] = path.replace(input_path,"").split("\\")[i]
                                columns += 1
                            file_date[index][columns] = file
                            index += 1
                            
                    # if input_path.endswith("\\"):
                    #     columns = path.replace(input_path,"").count("\\")
                    # else:
                    #     columns = path.replace(input_path,"").count("\\") - 1
                    # # print(columns)
                    # end_path = ntpath.basename(path)
                    # # print(end_path)
                    # file_date[index][columns] = end_path
                    # if files:
                    #     columns += 1
                    #     for file in files:
                    #         index += 1
                    #         file_date[index][columns]=file
                    #         if "Ver" in file:
                    #             file_date[index][0]=file.split("Ver")[0]
                    #         elif "ver" in file:
                    #             file_date[index][0]=file.split("ver")[0]
                    #         else:
                    #             file_len = len(file.split("."))
                    #             if file_len > 2:
                    #                 index_str = ""
                    #                 for i in range(0,file_len-1):
                    #                     index_str += file.split(".")[i]
                    #                 file_date[index][0]=index_str
                    #             else:
                    #                 file_date[index][0]=file.split(".")[0]
                            
                if add_sheet == 0:
                    add_sheet += 1
                    write_excel_xlsx(output_file, dir, file_date)
                else:
                    addExcel(output_file, file_date, dir)
    else:
        pass
    
    value3 = [["姓名", "性别", "年龄", "城市", "职业"],
          ["111", "女", "66", "石家庄", "运维工程师"],
          ["222", "男", "55", "南京", "饭店老板"],
          ["333", "女", "27", "苏州", "保安"],]

    # write_excel_xlsx(output_file, "new_sheet_name", value3)



